//用来处理博客前台业务的sql语句
const db = require("../app/database");
const crypto = require("crypto");
class ClientService {
  //显示首页头部分类导航
  async getCategories(){
    let sqlStatement  = 'select * from category order by `index` desc';
    const results = await db.execute(sqlStatement);
    return results[0];
  }
  //获取热门推荐的文章列表
  async getHotList(){
    let sqlStatement = 'select id,title,content,time from article where hot = 1';
    const results = await db.execute(sqlStatement);
    return results[0];
  }
  //获取最新博文的文章列表
  async getNewList(){
    let sqlStatement = 'select id,title,content,time from article order by `time` desc';
    const results = await db.execute(sqlStatement);
    return results[0];
  }
  //访问指定类目的文章列表
  async getListByCategory(id){
    let sqlStatement = 'select a.id,a.title,a.content,a.time,c.name from article a left join category c on a.category_id = c.id where c.id = ?';
    const results = await db.execute(sqlStatement,[id]);
    return results[0];
  }
  //通过关键词搜索文章列表
  async getListBySearch(keyword){
    let sqlStatement = `select id,title,content,time from article where title like '%${keyword}%'`;
    const results = await db.execute(sqlStatement,[keyword]);
    return results[0];
  }
  //获取文章详情
  async getDetailById(id){
    //查找文章详情和文章所属分类
    let sql1 = 'select a.id,a.title,a.content,a.time,a.hits,a.category_id,c.name categoryName from article a left join category c on a.category_id = c.id where a.id = ?';
    //查找文章的标签
    let sql2 = 'select * from tabs where article_id = ?';
    //查找上一篇和下一篇的文章
    let sql3 = 'select id,title from article where id = ?';
    const res1 = await db.execute(sql1,[id]);
    const res2 = await db.execute(sql2,[id]);
    const res3 = await db.execute(sql3,[id-1]);
    const res4 = await db.execute(sql3,[id+1]);
    return [res1[0],res2[0],res3[0],res4[0]];
  }
  //验证用户是否可以注册
  async verifyRegister(username){
    let sqlStatement = `select * from user where username = ?`;
    const results = await db.execute(sqlStatement,[username]);
    return results[0];
  }
  //真正的注册用户
  async registerUser(username,password){
    //对密码先进行md5加密
    password = crypto.createHash("md5").update(password).digest("hex");
    let sqlStatement = 'insert into user (username,password) values (?,?)';
    const results = await db.execute(sqlStatement,[username,password]);
    return results[0];
  }
  //验证用户登录
  async verifyLogin(username,password){
    //先对传过来的密码进行加密
    password = crypto.createHash("md5").update(password).digest("hex");
    let sqlStatement = 'select * from user where username = ? and password = ?';
    const results = await db.execute(sqlStatement,[username,password]);
    return results[0];
  }
}
module.exports = new ClientService();